﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Jy123.DBUtility;
using Jy123.BLL.User;
using Jy123.Model;
using Jy123.Common.Config;
using Jy123.BLL.Public;

namespace Jy123.BLL.User
{
    public class UserSql
    {
        /// <summary>
        /// 获取用户实体
        /// </summary>
        /// <param name="UserID">用户ID</param>
        /// <returns>返回用户实体</returns>
        public Model.User_Cross_MainAnPhoto GetUserInfo(int UserID)
        {
            Model.User_Cross_MainAnPhoto modelUserAnPhoto = new Jy123.Model.User_Cross_MainAnPhoto();
            modelUserAnPhoto.ID = UserID;
            string sql = "select a.*,b.FilePath from User_main a left join User_Photo b on a.Portrait=b.ID and b.IsLock=0 where a.ID=" + UserID;
            IDataReader rd = DBUtility.DbHelperSQL.ExecuteReader(sql, null);
            if (rd.Read())
            {
                modelUserAnPhoto.Email = rd["Email"].ToString();
                modelUserAnPhoto.Password = rd["Password"].ToString();
                modelUserAnPhoto.UserName = rd["Username"].ToString();
                modelUserAnPhoto.State = (int)rd["State"];
                if (rd["LastLoginTime"] != DBNull.Value) modelUserAnPhoto.LastLoginTime = (DateTime)rd["LastLoginTime"];
                if (rd["LastLoginIp"] != DBNull.Value) modelUserAnPhoto.LastLoginIP = rd["LastLoginIp"].ToString();
                modelUserAnPhoto.LoginTimes = (int)rd["LoginTimes"];
                modelUserAnPhoto.Province = (int)rd["Province"];
                if (rd["City"] != DBNull.Value) modelUserAnPhoto.City = (int)rd["City"];
                modelUserAnPhoto.RegTime = (DateTime)rd["RegTime"];
                modelUserAnPhoto.RegIP = rd["RegIP"].ToString();

                //判断用户是否有头像
                modelUserAnPhoto.Portrait = new Jy123.BLL.User.SPace().DefaultPortrait(UserID);

                if (rd["Portrait"] != DBNull.Value) modelUserAnPhoto.Portrait = rd["Portrait"].ToString();
                if (rd["FilePath"] != DBNull.Value) modelUserAnPhoto.PortraitDir = (string)rd["FilePath"];
                if (rd["integral"] != DBNull.Value) modelUserAnPhoto.integral = (int)rd["integral"];
                if (rd["Cash"] != DBNull.Value) modelUserAnPhoto.Cash = (int)rd["Cash"];
                if (rd["Click"] != DBNull.Value) modelUserAnPhoto.Click = (int)rd["Click"];
                if (rd["InviterID"] != DBNull.Value) modelUserAnPhoto.InviterID = (int)rd["InviterID"];
            }
            rd.Close();
            return modelUserAnPhoto;
        }
        /// <summary>
        /// 用户注册
        /// </summary>
        /// <param name="ui">用户对象</param>
        /// <param name="bi">用户个人资料</param>
        /// <returns>所影响的行数</returns>
        public EnumRegister Register(Jy123.Model.User_Main ui, Jy123.Model.User_Info bi,EnumPrivacy privacy, out int id)
        {
            string verifyCode = Jy123.Common.Rand.Str(11);
            string RegVert = Jy123.Common.Public.GetXMLValue("RegVer");
            SqlParameter[] param = new SqlParameter[15];
            param[0] = new SqlParameter("@ReturnValue", SqlDbType.Int);
            param[0].Direction = ParameterDirection.ReturnValue;
            param[1] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            param[1].Value = ui.Email;
            param[2] = new SqlParameter("@IP", SqlDbType.Char, 15);
            param[2].Value = Jy123.Common.Public.GetClientIP();
            param[3] = new SqlParameter("@Password", SqlDbType.NVarChar, 32);
            param[3].Value = Jy123.Common.Input.MD5(ui.Password, false);
            param[4] = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
            param[4].Value = ui.UserName;
            param[5] = new SqlParameter("@State", SqlDbType.Int, 4);
            int isYanZhen = 0;
            if (RegVert == "1")
            {
                isYanZhen = 0;
            }
            else
            {
                isYanZhen = 2;
            }
            param[5].Value = isYanZhen;
            param[6] = new SqlParameter("@Province", SqlDbType.Int, 4);
            param[6].Value = ui.Province;
            param[7] = new SqlParameter("@City", SqlDbType.Int, 4);
            if (ui.City == 0)
                param[7].Value = DBNull.Value;
            else
                param[7].Value = ui.City;
            param[8] = new SqlParameter("@NowTime", SqlDbType.DateTime);
            param[8].Value = DateTime.Now;
            param[9] = new SqlParameter("@VerifyCode", SqlDbType.Char, 11);
            param[9].Value = verifyCode;
            param[10] = new SqlParameter("@EmailItem", SqlDbType.VarChar, 1000);
            param[10].Value = EnumClass.GetEmailNotifyStr();
            param[11] = new SqlParameter("@PrivDef", SqlDbType.Int);
            param[11].Value = (int)privacy;
            param[12] = new SqlParameter("@Sex", SqlDbType.Int);
            param[12].Value = bi.Sex;
            param[13] = new SqlParameter("@IsRec", SqlDbType.Int);
            param[13].Value = ui.isRec;
            param[14] = new SqlParameter("@ID", SqlDbType.Int);
            param[14].Direction = ParameterDirection.Output;
            int rowsAffect = 0;
            DBUtility.DbHelperSQL.RunProcedure("SP_User_Register", param, rowsAffect);
            id = Convert.ToInt32(param[14].Value);
            int retvalue = (int)param[0].Value;
            switch (retvalue)
            {
                case 1:
                    #region 注册成功发送邮件
                    //try
                    //{
                    //    int actv = 0;
                    //    //插入默认好友
                    //    if (isYanZhen == 2)
                    //    {
                    //        string qsql = "select UserID from NT_User where Email='" + ui.Email + "'";
                    //        int UserID = Convert.ToInt32(DbHelperSQL.ExecuteSql(qsql, param));
                    //        UserSql us = new UserSql();

                    //        us.InserDefaultFriend(UserID);
                    //        actv = 0;
                    //    }
                    //    else
                    //    {
                    //        actv = 1;
                    //    }
                    //    if (isEmailVer)
                    //    {
                    //        Jy123.Common.EMail em = Jy123.Common.EMail.CreateInstance();
                    //        string body = EmailConfig.register;
                    //        body = body.Replace("{SiteName}", UiConfig.SiteName);
                    //        body = body.Replace("{UserName}", ui.UserName);
                    //        body = body.Replace("{Domain}", UiConfig.Domain);
                    //        body = body.Replace("{Date}", DateTime.Now.ToString("yyyy年MM月dd日"));
                    //        body = body.Replace("{Url}", UiConfig.RootUrl + "/RegVerify.aspx?active=" + actv + "&r=" + verifyCode + "&e=" + Jy123.Common.Input.URLEncode(ui.Email));
                    //        em.Body = body;
                    //        em.Subject = "亲爱的" + ui.UserName + "，请确认你在" + UiConfig.SiteName + "的注册邮箱";
                    //        em.To = ui.Email;
                    //        em.From = UiConfig.SiteName + "<" + EmailConfig.from + ">";
                    //        em.Send();
                    //        return EnumRegister.Succeed;
                    //    }
                    //    else
                    //    {
                    //        return EnumRegister.SucceedNotMail;
                    //    }
                    //}
                    //catch
                    //{
                    //    return EnumRegister.EmailMayNotSend;
                    //}
                    #endregion
                    return EnumRegister.Succeed;
                case 2:
                    return EnumRegister.EmailRepeat;
                default:
                    return EnumRegister.UnexpectedError;

            }
        }

        #region 检查登录
        /// <summary>
        /// 检查登录
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="password">用户登陆密码</param>
        /// <returns>返回登录状态</returns>
        public EnumLoginState CheckLogin(int userId, string password)
        {
            int loginNum = 0;
            return CheckLogin(userId, password, out loginNum);
        }
        /// <summary>
        /// 用户登陆
        /// </summary>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <param name="userId"></param>
        /// <param name="userName"></param>
        /// <param name="portrait"></param>
        /// <param name="loginNum"></param>
        /// <returns>返回登录状态</returns>
        public EnumLoginState Login(string email, string password, out int userId, out string userName, out string portrait, out int loginNum)
        {
            userId = -1;
            userName = string.Empty;
            portrait = string.Empty;
            loginNum = 0;
            SqlConnection cn = new SqlConnection(DBUtility.DbHelperSQL.connectionString);
            cn.Open();
            try
            {
                string pwd = string.Empty;
                EnumUserState us = EnumUserState.Lock;
                SqlParameter param = new SqlParameter("@Email", email);
                bool exist = false;
                string headpic = "";
                string Sql = "select ID,UserName,Password,State,Portrait,loginTimes from User_Main where Email=@Email";
                IDataReader rd = DBUtility.DbHelperSQL.ExecuteReader(Sql, param);
                //SqlDataReader rd = DBUtility.DbHelperSQL.ExecuteReader(Sql, param);
                if (rd.Read())
                {
                    userId = rd.GetInt32(0);
                    userName = rd.GetString(1);
                    pwd = rd.GetString(2);
                    loginNum = rd.GetInt32(5);
                    us = (EnumUserState)rd.GetInt32(3);
                    if (!rd.IsDBNull(4)) headpic = rd.GetString(4);
                    exist = true;
                }
                rd.Close();
                if (!exist || pwd == string.Empty || pwd != Jy123.Common.Input.MD5(password, false))
                {
                    return EnumLoginState.Err_NameOrPwdError;
                }
                if (us == EnumUserState.Register)
                {
                    return EnumLoginState.Err_UnActivation;
                }
                else if (us == EnumUserState.Lock)
                {
                    return EnumLoginState.Err_Locked;
                }
                if (!string.IsNullOrEmpty(headpic))
                {
                    portrait = headpic;
                }
                Sql = "update User_Main set LastLoginTime='" + DateTime.Now + "',LastLoginIP='" + Jy123.Common.Public.GetClientIP() + "',LoginTimes=LoginTimes+1 where ID=" + userId;
                DbHelperSQL.ExecuteSql(Sql);
                return EnumLoginState.Succeed;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }
        /// <summary>
        /// 检查登录
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="password">用户密码</param>
        /// <returns>返回用户状态</returns>
        public EnumLoginState CheckLogin(int userId, string password, out int loginNum)
        {
            SqlConnection cn = new SqlConnection(DBUtility.DbHelperSQL.connectionString);
            cn.Open();
            try
            {
                int lNum = 0;
                string pwd = string.Empty;
                EnumUserState us = EnumUserState.Lock;
                bool exist = false;
                string Sql = "select Password,State,LoginTimes from User_Main where ID=" + userId;
                IDataReader rd = DBUtility.DbHelperSQL.ExecuteReader(Sql, null);
                if (rd.Read())
                {
                    pwd = rd.GetString(0);
                    us = (EnumUserState)rd.GetInt32(1);
                    exist = true;
                    lNum = rd.GetInt32(2);
                }
                loginNum = lNum;
                rd.Close();
                if (!exist || pwd == string.Empty || pwd != password)
                    return EnumLoginState.Err_NameOrPwdError;
                if (us == EnumUserState.Register)
                    return EnumLoginState.Err_UnActivation;
                else if (us == EnumUserState.Lock)
                    return EnumLoginState.Err_Locked;
                return EnumLoginState.Succeed;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }
        #endregion

        /// <summary>
        /// 查看隐私是否被允许查看
        /// </summary>
        /// <param name="privType">隐私类型</param>
        /// <param name="userid">用户ID</param>
        /// <param name="visitUserid">被访问的用户ID</param>
        /// <returns>true/false</returns>
        protected bool PrivacyAvailable(EnumPrivacyType privType, int userid, int visitUserid)
        {
            if (userid == visitUserid)
                return true;
            //Home.User u = new Home.User();
            BLL.User_Setting bllUserSetting = new User_Setting();
            //Home.Friend fr = new JuSNS.Home.Friend();

            //Model.User_Friend modelUserFriend = new Jy123.Model.User_Friend();

            Model.User_Setting modelSetting = bllUserSetting.GetModel(visitUserid);
            switch (privType)
            {
                case EnumPrivacyType.Educate:
                    switch ((EnumPrivacy)modelSetting.PrivEducate)
                    {
                        case EnumPrivacy.ForFriends:
                            if (Friend.IsFriends(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForNetWorkAndFriends:
                            if (Friend.IsInSameNetwork(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForOwner:
                            if (userid == visitUserid)
                                return true;
                            break;
                        case EnumPrivacy.ForWholeSite:
                            return true;
                    }
                    break;
                case EnumPrivacyType.Favourite:
                    switch ((EnumPrivacy)modelSetting.PrivFavourite)
                    {
                        case EnumPrivacy.ForFriends:
                            if (Friend.IsFriends(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForNetWorkAndFriends:
                            if (Friend.IsInSameNetwork(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForOwner:
                            if (userid == visitUserid)
                                return true;
                            break;
                        case EnumPrivacy.ForWholeSite:
                            return true;
                    }
                    break;
                case EnumPrivacyType.Friends:
                    switch ((EnumPrivacy)modelSetting.PrivFriends)
                    {
                        case EnumPrivacy.ForFriends:
                            if (Friend.IsFriends(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForNetWorkAndFriends:
                            if (Friend.IsInSameNetwork(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForOwner:
                            if (userid == visitUserid)
                                return true;
                            break;
                        case EnumPrivacy.ForWholeSite:
                            return true;
                    }
                    break;
                case EnumPrivacyType.Group:
                    switch ((EnumPrivacy)modelSetting.PrivGroup)
                    {
                        case EnumPrivacy.ForFriends:
                            if (Friend.IsFriends(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForNetWorkAndFriends:
                            if (Friend.IsInSameNetwork(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForOwner:
                            if (userid == visitUserid)
                                return true;
                            break;
                        case EnumPrivacy.ForWholeSite:
                            return true;
                    }
                    break;
                case EnumPrivacyType.Lasso:
                    switch ((EnumPrivacy)modelSetting.PrivLasso)
                    {
                        case EnumPrivacy.ForFriends:
                            if (Friend.IsFriends(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForNetWorkAndFriends:
                            if (Friend.IsInSameNetwork(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForOwner:
                            if (userid == visitUserid)
                                return true;
                            break;
                        case EnumPrivacy.ForWholeSite:
                            return true;
                    }
                    break;
                case EnumPrivacyType.LeaveWord:
                    switch ((EnumPrivacy)modelSetting.PrivLeaveWord)
                    {
                        case EnumPrivacy.ForFriends:
                            if (Friend.IsFriends(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForNetWorkAndFriends:
                            if (Friend.IsInSameNetwork(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForOwner:
                            if (userid == visitUserid)
                                return true;
                            break;
                        case EnumPrivacy.ForWholeSite:
                            return true;
                    }
                    break;
                case EnumPrivacyType.MiniBlog:
                    switch ((EnumPrivacy)modelSetting.PrivMiniBlog)
                    {
                        case EnumPrivacy.ForFriends:
                            if (Friend.IsFriends(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForNetWorkAndFriends:
                            if (Friend.IsInSameNetwork(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForOwner:
                            if (userid == visitUserid)
                                return true;
                            break;
                        case EnumPrivacy.ForWholeSite:
                            return true;
                    }
                    break;
                case EnumPrivacyType.Share:
                    switch ((EnumPrivacy)modelSetting.PrivShare)
                    {
                        case EnumPrivacy.ForFriends:
                            if (Friend.IsFriends(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForNetWorkAndFriends:
                            if (Friend.IsInSameNetwork(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForOwner:
                            if (userid == visitUserid)
                                return true;
                            break;
                        case EnumPrivacy.ForWholeSite:
                            return true;
                    }
                    break;
                case EnumPrivacyType.Space:
                    switch ((EnumPrivacy)modelSetting.PrivSpace)
                    {
                        case EnumPrivacy.ForFriends:
                            if (Friend.IsFriends(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForNetWorkAndFriends:
                            if (Friend.IsInSameNetwork(userid, visitUserid))
                                return true;
                            break;
                        case EnumPrivacy.ForOwner:
                            if (userid == visitUserid)
                                return true;
                            break;
                        case EnumPrivacy.ForWholeSite:
                            return true;
                    }
                    break;
            }
            return false;
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="strWhere">筛选条件</param>
        /// <param name="cpage">起始页</param>
        /// <param name="psize">页记录大小</param>
        /// <param name="counts">获取数量</param>
        /// <returns>数据集</returns>
        public DataSet GetUserList(string strWhere, int cpage, int psize, out List<int?> counts)
        {
            return Pagination.GetList(new Jy123.Model.MPagination()
            {
                Filter = (string.IsNullOrEmpty(strWhere)) ? null : strWhere,
                //"email like '%" + key + "%'",
                CurrentPage = cpage,
                Fields = "*",
                PageSize = psize,
                PrimaryKey = "ID",
                //Sort = "PostTime DESC",
                Tables = "User_Main"
            }, out counts);
        }
        /// <summary>
        /// 检查密码是否正确
        /// </summary>
        /// <param name="email">输入的EMAIL</param>
        /// <param name="psw">输入的密码</param>
        /// <returns>true为正确/false为错误</returns>
        public bool CheckPassword(string email, string psw)
        {
            string password = Jy123.Common.Input.MD5(psw, false);
            string strSql = "SELECT ID, Email, Password, UserName FROM User_Main WHERE (Password = @Psw) AND (Email = @Email)";
            SqlParameter[] parameters = {
                                        new SqlParameter("@Email",SqlDbType.VarChar,200),
                                        new SqlParameter("@Psw",SqlDbType.VarChar,200)};
            parameters[0].Value = email;
            parameters[1].Value = password;
            int affectRow =0;
            affectRow = Convert.ToInt32(DBUtility.DbHelperSQL.GetSingle(strSql, parameters));
            if (affectRow != 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 检查EMAIL是否可注册
        /// </summary>
        /// <param name="email"></param>
        /// <returns>false已存在,不可注册/true不存在,可以注册</returns>
        public bool CheckEmailAvailability(string email)
        {
            string strSql = "SELECT Count(*) FROM User_Main WHERE Email = @Email";
            SqlParameter[] parameters = {
                                        new SqlParameter("@Email",SqlDbType.VarChar,200)};
            parameters[0].Value = email;
            int affectRow = 0;
            affectRow = Convert.ToInt32(DBUtility.DbHelperSQL.GetSingle(strSql, parameters));

            //return false;
            if (affectRow != 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 表情代码转换
        /// </summary>
        /// <param name="blogContent">传入原始日志内容</param>
        /// <returns>字符串</returns>
        public static string BlogContent(string blogContent)
        {
            Dictionary<string, Jy123.Model.User_Face> dicFace = new Dictionary<string, Jy123.Model.User_Face>();
            Jy123.Model.User_Face modelFace = new Jy123.Model.User_Face();
            Jy123.BLL.User_Face bllFace = new Jy123.BLL.User_Face();
            List<Jy123.Model.User_Face> listFace = bllFace.GetModelList(string.Empty);
            foreach (Jy123.Model.User_Face mFace in listFace)
            {
                blogContent = blogContent.Replace(mFace.InCode,"<img src="+mFace.FilePath+" />");                
            }           
            return blogContent;
        }
        /// <summary>
        /// 表情列表
        /// </summary>
        /// <returns>字符串</returns>
        public  string FaceList(string strHtml)
        {
            StringBuilder sbHtml = new StringBuilder();
             //= Cache["user_face"].ToString();
            //Jy123.Model.User_Face mUserFace = new Jy123.Model.User_Face();
            Jy123.BLL.User_Face bllUserFace = new Jy123.BLL.User_Face();
            List<Jy123.Model.User_Face> listUserFace = bllUserFace.GetModelList("");
            foreach (Jy123.Model.User_Face mUserFace in listUserFace)
            {
                string str = strHtml;
                str = str.Replace("$InCode$", mUserFace.InCode);
                str = str.Replace("$Title$", mUserFace.Title);
                str = str.Replace("$FilePath$", mUserFace.FilePath);
                sbHtml.Append(str);
            }
            return sbHtml.ToString();
        }
        /// <summary>
        /// 根据用户的DNA码获取用户ID
        /// </summary>
        /// <param name="verifyCode">用户DNA码</param>
        /// <returns>用户ID</returns>
        public int GetUserIDByVeryfyCode(string verifyCode)
        {
            string strSql = "SELECT ID FROM User_Main WHERE (VerifyCode=@VerifyCode)";
            SqlParameter[] parameters = { 
                                        new SqlParameter("@VerifyCode",SqlDbType.VarChar)};
            parameters[0].Value = verifyCode;
            return (int)DBUtility.DbHelperSQL.GetSingle(strSql, parameters);
        }
    }
}
